package com.yf1706.dao.jpa;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.yf1706.bean.User;

public interface IUserJDAO extends CrudRepository<User, Integer>{

	public User findByUserIdAndPassword(int userId, String psw);

	public User findByUserId(int userId);
	
	//查询用户是否拥有某个角色身份
	@Query(nativeQuery = true, value = "select u.user_id,e.emp_name,"
			+ "(select count(*) from user_role ur where ur.role_id=:roleId and "
			+ "u.user_id=ur.user_id) owner from user u inner join emp e "
			+ "on e.emp_id=u.emp_id")
	public List<Object[]> findUserEmpRoleByRoleId(int roleId);
	
	//查询所有用户的姓名及部门
	@Query(nativeQuery = true, value = "select u.user_id,e.emp_name,d.dep_id,d.dep_name "
			+ "from user u inner join emp e on u.emp_id=e.emp_id "
			+ "inner join dep d on d.dep_id=e.dep_id;")
	public List<Object[]> fundUserBasic();
	

	//查询所有用户的姓名及部门
	@Query(nativeQuery = true, value = "select u.user_id,e.emp_name,d.dep_id,d.dep_name "
			+ "from user u inner join emp e on u.emp_id=e.emp_id "
			+ "inner join dep d on d.dep_id=e.dep_id where u.user_id=:userId")
	public List<Object[]> fundUserBasicByUserId(int userId);
}
